﻿<html DIR="LTR" xmlns:tool="http://www.microsoft.com/tooltip" xmlns:xlink="http://www.w3.org/1999/xlink" xmlns:ddue="http://ddue.schemas.microsoft.com/authoring/2003/5" xmlns:MSHelp="http://msdn.microsoft.com/mshelp">
  <head>
    <META HTTP-EQUIV="Content-Type" CONTENT="text/html; CHARSET=utf-8" />
    <META NAME="save" CONTENT="history" />
    <title>Readme_Excel2 Custom Connection Manager Sample</title>
    
    
    
    <style TYPE="text/css">
body
{
background: #FFFFFF;
color: #000000;
font-family:    Verdana;
font-size: medium;
font-style: normal;
font-weight: normal;
margin-top: 0;
margin-bottom:  0;
margin-left:    0;
margin-right:   0;
width:  100%;
}

div.#mainSection
{
font-size: 70%;
width: 100%;
padding-left:    10;
margin-right: 10;
}

div.#mainBody
{
font-size: 90%;
margin-top: 10;
padding-bottom: 20;
}

div.#header
{
background-color: #D2D2D2;
padding-top:    0;
padding-bottom: 0;
padding-left:   10;
padding-right:  0;
width:          100%;
}

div.#header table
{
border-bottom-color: #C8CDDE;
border-bottom-style: solid;
border-bottom-width: 1;
width:  100%;
}

span.#runningHeaderText
{
color: #003399;
font-size: 90%;
}

span.#nsrTitle
{
/*    color: #003399;*/
font-size: 120%;
font-weight: 600;
}

div.#header table td
{
color: #000000;
font-size: 70%;
margin-top: 0;
margin-bottom:  0;
padding-right: 20;
}

div.#header table tr.#headerTableRow3 td
{
padding-bottom: 2;
padding-top: 5;
}

div.#header table.#bottomTable
{
border-top-color: #FFFFFF;
border-top-style: solid;
border-top-width: 1;
text-align: left;
}

div.#footer
{
font-size: 90%;
margin-top: 0;
margin-bottom:  0;
margin-left:    -5;
margin-right:   0;
padding-top:    2;
padding-bottom: 2;
padding-left:   0;
padding-right:  0;
width:  100%;
}

hr.#footerHR
{
border-bottom-color: #EEEEFF;
border-bottom-style: solid;
border-bottom-width: 1;
border-top-color: C8CDDE;
border-top-style: solid;
border-top-width: 1;
height: 3;
color: #D2D2D2;
}

div.section
{
padding-top:    2;
padding-bottom: 2;
padding-right:  15;
width:  100%;
}

.heading
{
color:          #000000;
font-weight:    bold;
margin-top:     18;
margin-bottom:  8;
}

h1.heading
{
color: #000000;
font-size:  150%;
}

.subHeading
{
color:          #000000;
font-weight:    bold;
font-size:      150%;
margin-bottom:  4;
}

h2.subHeading
{
color:          #000000;
font-weight:    bold;
font-size:      130%;
}
h3.subHeading
{
color:  #000000;
font-size: 125%;
font-weight: bold;
}

h4.subHeading
{
color: #000000;
font-size: 110%;
font-weight: bold;
}

h4.procedureHeading
{
color: #000080;
font-size: 110%;
font-weight: bold;
}

h5.subHeading
{
color: #000000;
font-size: 100%;
font-weight: bold;
}

img
{
padding-bottom: 10;
}

img.toggle
{
border: 0;
margin-right: 5;
padding-bottom: 10;
}

img.copyCodeImage
{
border: 0;
margin: 1;
margin-right: 3;
padding-bottom: 10;
}

img.downloadCodeImage
{
border: 0;
margin-right: 3;
padding-bottom: 10;
}

img.viewCodeImage
{
border: 0;
margin-right: 3;
padding-bottom: 10;
}

img.note
{
border: 0;
margin-right: 3;
padding-bottom: 10;
}

img.#membersOptionsFilterImage
{
border: 0;
margin-left: 10;
vertical-align: middle;
padding-bottom: 10;
}

img.#toggleAllImage
{
margin-left: 4;
vertical-align: middle;
padding-bottom: 10;
}

div.#mainSection table
{
border: 0;
font-size: 100%;
width:  100%;
margin-top: 5px;
margin-bottom: 15px;
}

div.#mainSection table tr
{
vertical-align: top;
}

div.#mainSection table th
{
text-align: left;
background: #D8D8D8;
border-bottom-color: #D8D8D8;
border-bottom-style: solid;
border-bottom-width: 1;
color: #000000;
padding-left: 5;
padding-right: 5;
}

div.#mainSection table td
{
background: #F2F2F2;
border-top-color: #D8D8D8;
border-top-style: solid;
border-top-width: 1;
padding-left: 5;
padding-right: 5;
}

div.#mainSection table td.imageCell
{
white-space: nowrap;
}

div.code
{
width: 98%;
}

div.code table
{
border: 0;
font-size: 95%;
margin-bottom: 5;
width: 100%
}

div.code table th
{
text-align: left;
background: #D8D8D8;
border-bottom-color: #D8D8D8;
border-bottom-style: solid;
border-bottom-width: 1;
color: #000000;
font-weight: bold;
padding-left: 5;
padding-right: 5;
}

div.code table td
{
background: #CCCCCC;
border-top-color: #D8D8D8;
border-top-style: solid;
border-top-width: 1;
padding-left: 5;
padding-right: 5;
padding-top: 5;
}

div.alert
{
margin-left: 10;
width: 98%;
}

div.alert table
{
border: 1;
font-size: 100%;
width:  100%;
border: solid 1 #DEDFEF;
}

div.alert table th
{
text-align: left;
background: #D8D8D8;
border-bottom-width: 0;
color: #000000;
padding-left: 5;
padding-right: 5;
border: solid 1 #DEDFEF;
}

div.alert table td
{
background: #FFFFFF;
border-top-color: #D8D8D8;
border-top-style: solid;
border-top-width: 1;
padding-left: 5;
padding-right: 5;
border: solid 1 #DEDFEF;
}

span.copyCode
{
color: #0000ff;
font-size: 90%;
font-weight: normal;
cursor: hand;
float: right;
display: inline;
text-align: right;
}

.downloadCode
{
color: #0000ff;
font-size: 90%;
font-weight: normal;
cursor: hand;
}

.viewCode
{
color: #0000ff;
font-size: 90%;
font-weight: normal;
cursor: hand;
}

div.code pre
{
font-family:    Monospace, Courier New, Courier;
font-size: 105%;
color:  #000000;
}

code
{
font-family:    Monospace, Courier New, Courier;
font-size: 105%;
color:  #000000;
}

dl
{
margin-top: 0;
padding-left:   1;
}

dd
{
margin-bottom:  0;
margin-left:    0;
padding-left:   20;
}

dd p
{
margin-top: 5;
}

ul
{
margin-left: 17;
list-style-type: disc;
}

ul ul
{
margin-bottom: 4;
margin-left: 17;
margin-top: 3;
list-style-type: disc;
}

ol
{
margin-left: 24;
list-style-type: decimal;
}

ol ol
{
margin-left: 24;
margin-top: 3;
list-style-type: lower-alpha;
}

li
{
margin-top: 0;
margin-bottom: 0;
padding-bottom: 0;
padding-top: 0;
margin-left: 5;
}

p
{
margin-bottom: 15;
}

.tip
{
color:  #0000FF;
font-style: italic;
cursor:hand;
text-decoration:underline;
}

.math
{
font-family: Times New Roman;
font-size: 125%
}
.sourceCodeList
{
font-family: Verdana;
font-size: 90%;
}

pre.viewCode
{
width: 100%;
overflow: auto;
}

li:hover table, li.over table
{
background-color: #C0C0C0;
}

li:hover ul, li.over ul
{
background-color: #d2d2d2;
border: 1px solid #000;
display: block;
}
            </style>
  </head>
  <body>
    <!--Topic built:08/05/2008 01:28:33-->

    
    
    
    
    
    
    
    
    
    <div id="header">
      <table width="100%" id="topTable"><tr>
          <td align="left">
            <span id="nsrTitle">Readme_Excel2 Custom Connection Manager Sample</span>
          </td>
          <td align="right">
            <span id="headfb" class="feedbackhead">
            </span>
          </td>
        </tr></table>
      
      
      
    </div>
    <div id="mainSection">
      <div id="mainBody"><p> 08/05/2008 01:28:33</p>
        
        <font color="DarkGray"> </font><p /> 
        <span id="changeHistory">
        </span>
    <p>
      This sample works only with SQL Server 2005 and SQL Server 2008. It will not work with any version of SQL Server earlier than SQL Server 2005.
    </p>
    <p>The Excel2 Custom Connection Manager sample demonstrates how to create a custom connection manager, the Excel2 Connection Manager, and its associated user interface.</p>
    <p>This sample is not supported on Itanium-based operating systems.</p>
    <p>For more information about how to create a custom connection manager, see <b>Creating a Custom Connection Manager</b>. For general information about the use of connection managers in Integration Services, see <b>Integration Services Connections</b> and <b>Connection Managers</b>.</p>
    <p>The custom connection manager connects to an available Microsoft Excel workbook by using the OleDb .NET Framework Data Provider and the Microsoft OLE DB Provider for Jet, and returns an open <b>OleDbConnection</b> object from its <b>AcquireConnection</b> method. When you create a new instance of the Excel2 Connection Manager in a package, its custom user interface prompts you for the path of the Excel workbook. This connection manager also provides a check box to specify Import Mode. This resolves an issue that occurs frequently when an Excel column contains mixed text and numeric data.</p>
    <div class="alert"><table width="100%" cellspacing="0" cellpadding="0"><tr><th align="left">Note: </th></tr><tr><td>
      The Excel2 Custom Connection Manager sample cannot be used with the Excel source and destination. Test it with the ADO NET source and destination instead.<p />
    </td></tr></table><p /></div>
    <p>This code sample implements the following functionality:</p>
    <ul><li>
        Overriding of the <b>ConnectionManagerBase</b> class.<br />
      </li><li>
        Persistence support.<br />
      </li></ul>
    <div class="alert"><table width="100%" cellspacing="0" cellpadding="0"><tr><th align="left">Important: </th></tr><tr><td>
      Samples are provided for educational purposes only. They are not intended to be used in a production environment and have not been tested in a production environment. Microsoft does not provide technical support for these samples.<p />
    </td></tr></table><p /></div>
  <h1 class="heading">Running the Sample</h1><div id="sectionSection0" class="section"><content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
      <p xmlns="">If you already know how to locate, build, and install code samples, you can go directly to the section, <a href="#testing">Testing the Sample</a>, and read about how to configure and run this sample.</p>
    </content></div><h1 class="heading">Prerequisites</h1><div id="sectionSection1" class="section"><content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
      <p xmlns="">This sample requires that the following components are installed.</p>
      <ul xmlns=""><li>
          
            Microsoft Visual Studio<br />
        </li><li>
          
            Microsoft SQL Server Integration Services <br />
        </li></ul>
    </content></div><h1 class="heading">Location</h1><div id="sectionSection2" class="section"><content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
      <p xmlns="">If the code sample was installed to the default location, the sample is located in the following directory:</p>
      <p xmlns="">
        C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services\Programming Samples\Control Flow\Excel2ConnectionManager Sample</p>
      <p xmlns="">The C# solution for the code sample is located in the Excel2ConnMgrCS directory, and the Visual Basic solution is located in the Excel2ConnMgrVB directory.</p>
      <p xmlns="">For information about the two-step process required to install the samples, see <a href="html/e8117979-fe03-441e-be85-894fa616227a.htm">Considerations for Installing SQL Server Samples and Sample Databases</a>. To obtain the latest version of the samples, including new samples released after the original release of SQL Server, see the <a href="http://go.microsoft.com/fwlink/?LinkId=85384" alt=""><linkText xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">Microsoft SQL Server Samples and Community Projects</linkText></a> Web site.</p>
    </content></div><h1 class="heading">Building the Sample</h1><div id="sectionSection3" class="section"><content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
      <p xmlns="">If you have not already generated a strong name key file in the <b>Samples</b> folder, use the following procedure to generate this key file. The sample projects are configured on the Signing tab of the Project Properties dialog box to sign assemblies at build time with this key file.</p>
      <h4 class="procedureHeading" xmlns="">To generate a strong name key file</h4><div id="procedureSectionEDBHBHA" class="section" xmlns=""><ol><li>
            <content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
              <p xmlns="">To open a Microsoft Visual Studio command prompt, click <b>Start</b>, point to <b>All Programs</b>, point to <b>Microsoft Visual Studio 2008</b>, point to <b>Visual Studio Tools</b>, and then click <b>Visual Studio 2008 Command Prompt</b>.</p>
              <p xmlns="">- or -</p>
              <p xmlns="">To open a Microsoft .NET Framework command prompt, click <b>Start</b>, point to <b>All Programs</b>, point to <b>Microsoft .NET Framework SDK v2.0</b>, and then click <b>SDK Command Prompt</b>.</p>
            </content>
          </li><li>
            <content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
              <p xmlns="">At the command prompt, use the change directory (CD) command to change the current folder of the Command Prompt window to the <b>Samples</b> folder. The key file that you create in this folder will be used by all SQL Server code samples.</p>
              <div class="alert" xmlns=""><table width="100%" cellspacing="0" cellpadding="0"><tr><th align="left">Note: </th></tr><tr><td>
                To determine the folder where samples are located, click <b>Start</b>, point to <b>All Programs</b>, point to <b>Microsoft SQL Server 2008</b>, point to <b>Documentation and Tutorials</b>, and then click <b>Samples Directory</b>. If the default installation location was used, the samples are located in <i>&lt;drive&gt;</i>:\Program Files\Microsoft SQL Server\100\Samples.<p />
              </td></tr></table><p /></div>
              <p xmlns="">
              </p>
            </content>
          </li><li>
            <content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
              <p xmlns="">At the command prompt, run the following command to generate the key file:</p>
              <div class="code" xmlns=""><span codeLanguage="other"><table width="100%" cellspacing="0" cellpadding="0"><tr><th align="left" /></tr><tr><td colspan="2"><pre>sn -k SampleKey.snk
</pre></td></tr></table></span></div>
              <div class="alert" xmlns=""><table width="100%" cellspacing="0" cellpadding="0"><tr><th align="left">Important: </th></tr><tr><td>
                For more information about the strong-name key pair, see "Security Briefs: Strong Names and Security in the .NET Framework" in the .NET Development Center on MSDN.<p />
              </td></tr></table><p /></div>
              <p xmlns="">
              </p>
            </content>
          </li><li>
            <content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
              <p xmlns="">You will need the public key token from the key file in a subsequent step. To obtain the public key token, first extract the public key from the key file to a new file by running the following command at the command prompt:</p>
              <div class="code" xmlns=""><span codeLanguage="other"><table width="100%" cellspacing="0" cellpadding="0"><tr><th align="left" /></tr><tr><td colspan="2"><pre>sn -p SampleKey.snk SampleKeyPublic.snk</pre></td></tr></table></span></div>
              <p xmlns="">Now display the public key token from the new file by running the following command at the command prompt:</p>
              <div class="code" xmlns=""><span codeLanguage="other"><table width="100%" cellspacing="0" cellpadding="0"><tr><th align="left" /></tr><tr><td colspan="2"><pre>sn -t SampleKeyPublic.snk</pre></td></tr></table></span></div>
              <p xmlns="">Copy the public key token to the clipboard or save it for later use.</p>
            </content>
          </li></ol></div>
      <h4 class="procedureHeading" xmlns="">To build the sample in Microsoft Visual Studio 2008</h4><div id="procedureSectionEBBHBHA" class="section" xmlns=""><ol><li>
            <content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
              <p xmlns="">From the <b>File</b> | <b>Open</b> menu, click <b>Project</b> and open the <b>Excel2ConnMgrVB.sln</b> or <b>Excel2ConnMgrCS.sln</b>.</p>
            </content>
          </li><li>
            <content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
              <p xmlns="">In the Solution Explorer, select the <b>Excel2ConnMgrVB or Excel2ConnMgrCS</b> project.</p>
            </content>
          </li><li>
            <content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
              <p xmlns="">Locate the <b>DtsConnection</b> attribute before the class declaration in the Excel2ConnMgrCS.cs file or the Excel2ConnMgrVB.vb file and replace the alphanumeric value of the Public Key Token in the <b>UITypeName</b> property of the attribute with the public key token obtained earlier from the key file.</p>
            </content>
          </li><li>
            <content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
              <p xmlns="">On the <b>Build</b> menu, click <b>Build Solution</b> to build the solution.</p>
            </content>
          </li></ol>
          <content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
          </content>
        </div>
    </content></div><h1 class="heading">Installing the Sample</h1><div id="sectionSection4" class="section"><content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
      <p xmlns="">This sample is provided in both Visual Basic and C#. To distinguish the assemblies for each version of the sample, the name of the output assembly has CS or VB appended. After successfully building the component, follow these steps in order to add it as a connection manager in a package in Business Intelligence Development Studio.</p>
      <h4 class="procedureHeading" xmlns="">To copy the component to the Connections folder</h4><div id="procedureSectionEFBFBHA" class="section" xmlns=""><ol><li>
            <content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
              <p xmlns="">Open Windows Explorer or your preferred application for working in the file system.</p>
            </content>
          </li><li>
            <content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
              <p xmlns="">Copy the assemblies (Excel2ConnMgrCS.dll and Excel2ConnMgrUICS.dll, or Excel2ConnMgrVB.dll and Excel2ConnMgrUIVB.dll) to the <b>Connections</b> folder located at <b>%system%\Program Files\Microsoft SQL Server\100\DTS\Connections</b>.</p>
            </content>
          </li></ol></div>
      <h4 class="procedureHeading" xmlns="">To install the component into the global assembly cache (GAC) by dragging and dropping the assemblies</h4><div id="procedureSectionEDBFBHA" class="section" xmlns=""><ol><li>
            <content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
              <p xmlns="">Open Windows Explorer or your preferred application for working in the file system.</p>
            </content>
          </li><li>
            <content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
              <p xmlns="">Drag the assemblies from the Connections folder to the folder where the global assembly cache (GAC) is located, at <b>%system%\assembly</b>.</p>
            </content>
          </li></ol></div>
      <h4 class="procedureHeading" xmlns="">To install the component into the global assembly cache (GAC) by using gacutil.exe</h4><div id="procedureSectionEBBFBHA" class="section" xmlns=""><ol><li>
            <content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
              <p xmlns="">Open a Command Prompt window.</p>
            </content>
          </li><li>
            <content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
              <p xmlns="">Type the following commands to install the C# version of the component and the user interface into the GAC: </p>
              <div class="code" xmlns=""><span codeLanguage="other"><table width="100%" cellspacing="0" cellpadding="0"><tr><th align="left" /></tr><tr><td colspan="2"><pre>gacutil.exe -iF "C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services\Programming Samples\Control Flow\Excel2ConnectionManager Sample\CS\Excel2ConnMgrCS\bin\Debug\Excel2ConnMgrCS.dll"
gacutil.exe -iF "C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services\Programming Samples\Control Flow\Excel2ConnectionManager Sample\CS\Excel2ConnMgrUICS\bin\Debug\Excel2ConnMgrUICS.dll"</pre></td></tr></table></span></div>
              <p xmlns="">- or -</p>
              <p xmlns="">Type the following commands to install the Visual Basic version of the component and the user interface into the GAC:<b> </b></p>
              <div class="code" xmlns=""><span codeLanguage="other"><table width="100%" cellspacing="0" cellpadding="0"><tr><th align="left" /></tr><tr><td colspan="2"><pre>gacutil.exe -iF " C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services\Programming Samples\Control Flow\Excel2ConnectionManager Sample\VB\Excel2ConnMgrVB\bin\Debug\Excel2ConnMgrVB.dll"
gacutil.exe -iF " C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services\Programming Samples\Control Flow\Excel2ConnectionManager Sample\VB\Excel2ConnMgrVB\bin\Debug\Excel2ConnMgrUIVB.dll"</pre></td></tr></table></span></div>
            </content>
          </li></ol></div>
    </content></div><a name="testing" /><h1 class="heading">Testing the Sample</h1><div id="sectionSection5" class="section"><content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
      <p xmlns="">You can now test the custom connection manager in a package.</p>
      <h4 class="procedureHeading" xmlns="">To use the sample connection manager in a package</h4><div id="procedureSectionEBBDBHA" class="section" xmlns=""><ol><li>
            <content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
              <p xmlns="">In the connection managers area of SSIS Designer, right-click, select <b>New Connection</b>, and in the list of available connection types, select EXCEL2. Click <b>OK</b>.</p>
            </content>
          </li><li>
            <content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
              <p xmlns="">Edit the new connection manager. In the <b>Excel2 Connection Manager Editor</b>, locate an available Excel workbook file. Use the check boxes to specify settings for the headers and Import Mode settings.</p>
              <div class="alert" xmlns=""><table width="100%" cellspacing="0" cellpadding="0"><tr><th align="left">Note: </th></tr><tr><td>
                The Excel2 Custom Connection Manager sample cannot be used with the Excel source, which supports only the built-in Excel connection manager. Test it with the ADO NET source instead.<p />
              </td></tr></table><p /></div>
            </content>
          </li></ol></div>
    </content></div><!--[if gte IE 5]>
			<tool:tip element="seeAlsoToolTip" avoidmouse="false"/><tool:tip element="languageFilterToolTip" avoidmouse="false"/><tool:tip element="roleInfoSpan" avoidmouse="false"/>
		<![endif]--></div>
      <div id="footer">
			
			© 2008 Microsoft Corporation. All rights reserved.
		</div>
    </div>
  </body>
</html>